IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('ClientPro_Card_add_jz')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE ClientPro_Card_add_jz

GO
/*

1.20200509 lrh 调整新增档案没有卡片时虚拟卡处理，
*/

create procedure [dbo].[ClientPro_Card_add_jz]--发卡处理过程
(
@dev_serial char(7),		--发卡设备编号
@lx int,					--过程类型（0：预处理；1：更新结果状态; 2:同步发卡）
@ac_type char(16)=null,		--人员账户类型
@user_serial bigint,		--人员序号
@card_hao varchar(20),		--物理卡号
@jm_kh varchar(20),			--加密卡号
@card_serial char(8),		--卡顺序号
@card_lx int,				--卡类型
@card_work int,				--读写状态
@card_type int,				--卡状态
@ip  varchar(20),     		--操作IP
@gly_no nvarchar(50),		--操作员
@reg_serial varchar(50)		--企业编号
)
as
--返回值必须添加的内容
set nocount on

--变量定义
declare @ac_no char(16)			--账户编号
declare @rec_count int			--计数器
declare @card_order int			--卡号顺序
declare @xh int					--卡号表序号

declare @pxh varchar(8)      	 --卡父序号
declare @tt_form varchar(50)	 --卡转换格式
declare @tempkh varchar(50) 	 --转换后卡号

declare @tt_xh int          	 --卡转换格式序号
declare @tt_cut int				 --截取位数
declare @tt_hex int			 	 --进制
declare @tt_fields varchar(50)	 --

declare @user_no varchar(100)
declare @result int
declare @tmp_card varchar(50)
declare @module_card int
declare @card_serial_max varchar(50)
declare @count int
--变量赋值
set @rec_count=0
set @card_order=0


	if(@lx=0)	--发卡预处理过程
	begin

		--1.人员是否有卡
		if (select isnull(user_card,'') from dt_user where user_serial=@user_serial)<>''
		begin

			select 2
			return

		end

		--2.新卡是否占用
		if (select count(card_hao) from dt_card where card_hao=@card_hao and card_type<>4)>0
		begin

			select 3
			return

		end

		begin transaction

			---1.1插dt_card表数据
			--计算Card_order
			select @card_order=(max(card_order)+1) from dt_card where user_serial=@user_serial

			insert into dt_card(
			lx, gly_no, sj, user_serial, card_lx,
			card_type, card_hao, card_order, card_serial,
			loss_count, isCardReplace
			)values(
			0,@gly_no,getdate(),@user_serial,@card_lx,
			@card_type,@card_hao,@card_order,@card_Serial,
			0,0
			)

			--1.2写预处理日志
			insert into wt_card_log(
			lx,log_type,log_state,user_serial,dev_serial,card_old,
			card_new,log_xh,log_sj,log_ip,gly_no,log_lx,log_group,
			log_row,log_erro,bz,regserial
			)values(
			12,0,0,@user_serial,@dev_serial,@card_hao,
			@card_serial,@card_lx,getdate(),@ip,@gly_no,0,null,
			null,0,null,@reg_serial
			)

		commit transaction

	end

	else if(@lx=1)	--完成并更新结果
	begin

		begin transaction

			select @xh=max(xh) from dt_card where user_serial=@user_serial and card_serial=@card_serial
			and ((card_hao=@card_hao and card_type=4)or card_type=5)
			if(@card_type=5)
			begin

				update dt_card set card_type=@card_type,card_hao=''
				where xh=@xh

				delete from real_xf_cash where user_serial=@user_serial and card_serial=@card_serial

				commit transaction
				select 1
				return
			end

			else
			begin

				if(isnull(@ac_type,'')<>'')	--开户
				begin

					if exists(select 1 from dt_Ac_user where user_serial=@user_serial)
					begin

						update dt_ac_user set ac_type=@ac_type,ac_state=0,ac_jssj=dateadd(year,20,getdate())
						where user_serial=@user_serial

					end
					else
					begin
						while 1 = 1
						begin

							set @ac_no = replace(replace(replace(convert(varchar(19),getdate(),21),'-',''),':',''),' ','') + right(replicate('0', 1) + convert(varchar(10),ceiling(rand()*99)), 2)
							select @rec_count = count(*) from dt_ac_user where ac_no = @ac_no
							if @rec_count > 0
								begin
									waitfor delay '00:00:00.20'
									continue
								end
							else
							begin

								insert into dt_ac_user(ac_no,ac_type,user_serial,ac_pass,ac_kssj,ac_jssj,ac_state,sj,gly_no,ac_money,ac_make,ac_addo,ac_subo) select @ac_no,@ac_type,@user_serial,ac_pass,getdate() as kssj,case ac_unit when 0  then DATEADD(year,isnull(ac_limit,0),getdate()) when 1 then DATEADD(month,isnull(ac_limit,0),getdate()) when 2  then DATEADD(day,isnull(ac_limit,0),getdate()) end as jssj,0,getdate(),@gly_no,0,0,0,0 from DT_AC_TYPE where Ac_bh=@ac_type

								break
							end

						end
					end

				end

				---清除未决记录
				delete from real_xf_cash where user_serial=@user_serial and card_serial=@card_serial

				---更新卡表状态
				update dt_card set card_hao=@card_hao,card_type=@card_type,card_work=@card_work,card_lx=@card_lx,sj=getdate()
				where xh=@xh

				---生成转换格式数据
				exec ClientPro_Card_formate @xh,@card_lx

				---生成人员卡户
				--计算Card_order
				select @card_order=(max(card_order)+1) from dt_ac_card where user_serial=@user_serial
				insert into dt_ac_card(
				user_serial,card_serial,card_hao,card_order,ac_money,ac_addm,ac_addo,ac_subm,
				ac_subo,ac_regm,ac_make,ac_eachm,ac_eacho,ac_state,sj,ks_sj,gly_no
				)VALUES(
				@user_serial,@card_serial,@card_hao,isnull(@card_order,1),0,0,0,0,
				0,0,0,0,0,0,getdate(),getdate(),@gly_no
				)

				---更新人员表状态
				update dt_user set user_ac = 1,user_card=@card_hao,user_sj=getdate()
				where user_serial = @user_serial

				--生成增量档案日志
				 insert into wt_user_up(lx,log_type,log_finger,user_serial,card_serial,log_sj,log_ip,gly_no)
				 values(1,1,0,@user_serial,@card_serial,getdate(),@ip,@gly_no)

				--新增照片日志
				 if exists (select 1 from dt_user where user_serial=@user_serial and user_photo=1)
				 begin
				 insert into wt_user_up(lx,log_type,log_finger,user_serial,card_serial,log_sj,log_ip,gly_no)
				 values(1,2,0,@user_serial,@card_serial,getdate(),@ip,@gly_no)
				 end

				---更新发卡日志
				Update WT_CARD_LOG set lx=0,log_sj=getdate(),log_ip=@ip,gly_no=@gly_no,card_old=@card_hao
				where lx=12 and user_serial=@user_serial and card_new=@card_serial

			end

		commit transaction
	end

	else if(@lx=2)
	begin
		---判断人员信息

	    --1.人员是否有卡
	    select @user_no = user_no from dt_user where user_serial = @user_serial

        	--3.创建人员账户//////建议去掉！！！！！！！！！！！
		exec ClientPro_user_kh @ac_type,@user_serial,@gly_no,@result output
		if @result <> 0
		begin
			print '开户失败,卡号:'+@card_hao
			return				--返回
		end;

		begin transaction
		--4.生成卡信息
		--计算Card_order

		select @card_order=(max(card_order)+1) from dt_card where user_serial=@user_serial

		--20200509 lrh 调整虚拟卡处理
		if isnull(@card_order,'')=''
		begin
			set @card_order=1
		end

		set @card_hao=CAST(@user_serial as varchar)

		select @module_card = module_card+1 from wt_module where module_id='0009'
		set @card_serial = right('00000000'+convert(varchar(8),@module_card),8)  --右取8位 不足位前补0


		if not exists(select * from dt_card where user_serial=@user_serial and isnull(is_virtual_card ,0)=1 )
		begin
			insert into dt_card(lx, gly_no, sj, user_serial, card_lx,card_type, card_hao, card_order, card_serial,loss_count, isCardReplace,is_virtual_card)
			select 0,@gly_no,getdate(),@user_serial,23,0,@card_hao,@card_order,@card_serial ,0,0,1
			update wt_module set module_card=@module_card where module_id='0009'

		end

		--5.生成联机账户
		if (select count(1) from dt_ac_link where user_serial=@user_serial)=0
		begin
			insert into dt_ac_link(
			user_serial,card_serial,card_order,ac_money,ac_addm,ac_addo,ac_subm,
			ac_subo,ac_regm,ac_make,ac_eachm,ac_eacho,ac_state,sj,gly_no,card_hao
			)VALUES(
			@user_serial,@card_serial,@card_order,0,0,0,0,
			0,0,0,0,0,0,getdate(),@gly_no,@card_hao

			)
		end
		else
		begin

			update dt_ac_link set
			card_serial=@card_serial,
			card_hao=@card_hao
			where user_serial=@user_serial

		end

		--6.更新人员表状态
		update dt_user set user_ac = 1,user_card=@card_hao,user_sj=getdate()
		where user_serial = @user_serial


		--7.生成增量档案日志20151021
		insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
		values(1,1,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)

		--8.生成增量档案日志20200509
		insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
		values(8,4,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)

		commit transaction
	end


	if @lx <> 2
	select 1


GO

